GROUPING[_ID]

Purpose

With the use of this function you can distinguish between regular result rows and super-aggregate rows which are created in case of GROUPING SETS, CUBE or ROLLUP clauses.

Syntax

grouping::=

Grouping ID

Usage Notes

  • Each argument must be similar to an expression within the GROUP BY clause.
  • In case of a single argument the result value is 0 if the corresponding grouping considers this expression, otherwise 1 (superaggregation).
  • In case of multiple arguments the result value is a number whose binary representation is similar to GROUPING(arg1), GROUPING(arg2),..., GROUPING(argn). For example, the following is true:
    GROUPING(a,b,c) = 4xGROUPING(a) + 2xGROUPING(b) + 1xGROUPING(c)
  • For more information on GROUPING SETS, CUBE and ROLLUP, refer to the SELECT statement in the Query language (DQL) section.

Example

SELECT SUM(volume) revenue, y, m,
DECODE(GROUPING(y,m),1,'yearly',3,'total',NULL) superaggregate
FROM sales GROUP BY ROLLUP(y,m) ORDER BY y,revenue;
Result
REVENUE Y M SUPERAGGREGATE
1725.90 2010 December  
1725.90 2010   yearly
735.88 2011 April  
752.46 2011 February  
842.32 2011 March  
931.18 2011 January  
3261.84 2011   yearly
4987.74     total